<?php
/**
 * Created by PhpStorm.
 * User: R.Ice
 * Date: 2018/12/8
 * Time: 14:01
 * name:查询老师当月带班数
 * url:/erpTeacherClassRecord/teacherClassCount
 */

$monthStart = $route->restfulParams['monthStart'];  //月初时间戳

$monthEnd = $route->restfulParams['monthEnd'];      //月末时间戳

$uid = $route->restfulParams['uid'];

$classInfo = array();

//班级数量
$sql = "SELECT COUNT(*) AS class_sum ,osc_teacher_id,osc_id  FROM  art_oa_school_class WHERE  osc_teacher_id = $uid AND osc_start_time < $monthEnd AND osc_end_time>$monthStart";

$re = $db->mysqlDB->query($sql);

//print 1;
//var_dump($re);
if ($re[0]["class_sum"] > 0) {

    foreach ($re as $key => $val) {
        $re = $val;

        $osc_id = $val["osc_id"];
        //考勤数量
        $sql2 = "SELECT COUNT(*) AS course_sum FROM  art_oa_class_time WHERE  oct_oscid = " . $osc_id . " AND oct_start_time < $monthEnd AND oct_end_time>$monthStart";

        $re2 = $db->mysqlDB->query($sql2);
//        print 2;
//        var_dump($re2);
        //打卡数
        $sql4 = "SELECT COUNT(*) AS sign_sum FROM art_oa_teacher_sign WHERE  ots_ouid = $uid";

        $re4 = $db->mysqlDB->query($sql4);

//        print 3;
//        var_dump($re4);
        //学员id
        $sql11 = "SELECT ocs_osid FROM  art_oa_class_student WHERE  ocs_oscid = $osc_id";

        $re11 = $db->mysqlDB->query($sql11);

//        print 4;
//        var_dump($re11);
        for ($i = 0; $i < count($re11); $i++) {
            $array["ocs_osid"] .= $re11[$i]["ocs_osid"] . ',';
        }
        $osidArr = substr($array["ocs_osid"], 0, -1);
        //每个学员总定单数
        $allOrder = "SELECT oo_id FROM  art_oa_order WHERE  oo_osid IN ($osidArr)";

        $re_order = $db->mysqlDB->query($allOrder);

//        print 5;
        //每个学员当月单数
        $monthOrder = "SELECT oo_id FROM  art_oa_order WHERE  oo_osid IN ($osidArr) AND oo_create_time < $monthEnd AND oo_create_time>$monthStart";

        $re_monthOrder = $db->mysqlDB->query($monthOrder);

//        print 6;

        $orderArr = array();

        if(count($re_order)>1 && count($re_monthOrder)>0){
            array_push($orderArr,array("orderId"=>$re_monthOrder[0]["oo_id"]));
        }

        $renewRate = round(count($orderArr)/count($re11)*100,1);

        //已签到课时数量
        //------全部课时id
        $sql22 = "SELECT oct_id FROM  art_oa_class_time WHERE  oct_oscid = " . $osc_id . " AND oct_start_time < $monthEnd AND oct_end_time>$monthStart";

        $re22 = $db->mysqlDB->query($sql22);

//        print 7;
//        var_dump($re22);
        for ($i = 0; $i < count($re22); $i++) {
            $arr["oct_id"] .= $re22[$i]["oct_id"] . ',';
        }

        $newArr = substr($arr["oct_id"], 0, -1);
        //----已签到课时
        $sql44 = "SELECT COUNT(*) AS course_sum FROM  art_oa_student_class_time_check WHERE  osctc_octid IN ($newArr) AND osctc_create_time < $monthEnd AND osctc_create_time>$monthStart AND osctc_status = 0";

        $re44 = $db->mysqlDB->query($sql44);

//        print $re44;
//        var_dump($re44);
        //课程单价
        $sql33 = "SELECT SUM(oo_real_price) AS all_money,SUM(oo_course_times) AS all_course FROM  art_oa_order WHERE oo_ocsid = $osc_id AND oo_osid IN ($osidArr)";

        $re33 = $db->mysqlDB->query($sql33);

//        print $re33;
//        var_dump($re33);

        foreach ($re2 as $k => $v) {
            $re2 = $v;
        }

        foreach ($re4 as $k => $v) {
            $re4 = $v;

        }
        $re["course_sum"] = $re2["course_sum"];

        $singleCoursePrice = ($re33[0]['all_money'] / ($re33[0]['all_course'])) * $re2["course_sum"];

        if (intval($singleCoursePrice)==0) {
            $singleCoursePrice = 0;
        }

        $re['courseConsume'] = round($singleCoursePrice,1);

        $re["sign_sum"] = $re4["sign_sum"];

        $re["renewRate"] = $renewRate;
        $re["sql"] = $sql;


    }

    array_push($classInfo, $re);


}
$rs = array("count" => count($classInfo),"data" => $classInfo);

$response->responseData(true, $rs);
